SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[PCardReview]
@iK_disc int,
@iK_vid_zanyat int,
@n_sem int,
@Ik_grup int,
@nCode NUMERIC(18,0)
AS
SELECT DISTINCT
NReg[Регистрационный номер],
Cname_fac[Название факультета],
k.cname_kaf[Название кафедры],
Ss.Cname_spec[Специальность],
Pn.Clastname[Фамилия студента],
Pn.Cfirstname[Имя студента],
Pn.Cotch[Отчество студента],
Nn_zach[Шифр],
Cname_grup[Группа],
cName_vid_zanyat[Вид работы],
cName_disc[Название дисциплины],
KPTheme[Тема],
Pe.Clastname[Фамилия преподавателя],
Pe.Cfirstname[Имя преподавателя],
Pe.Cotch[Отчество преподавателя],
NameStatus[Статус в комиссии],
Mark[Оценка],
R.Comment[Рецензия]
FROM dbo.discpln dis INNER JOIN dbo.sv_disc sd
ON dis.iK_disc=sd.ik_disc 
 INNER JOIN dbo.Uch_pl Up 
  ON sd.ik_uch_plan=Up.ik_uch_plan
  INNER JOIN dbo.Grup gr
   ON Up.ik_uch_plan=gr.Ik_uch_plan
    INNER JOIN dbo.Spec_stud Ss
     ON Up.ik_spec=Ss.ik_spec
    INNER JOIN dbo.Content_UchPl cu
     ON sd.ik_disc_uch_plan=cu.ik_disc_uch_plan
      INNER JOIN dbo.vid_zaniat vz
       ON cu.ik_vid_zanyat=vz.iK_vid_zanyat
       INNER JOIN dbo.Semester sem
        ON cu.n_sem=sem.n_sem
         INNER JOIN dbo.UspevDocument ud
          ON cu.ik_upContent=ud.ik_upContent
           INNER JOIN dbo.UspevKPTheme ukp 
            ON ud.idUspevDocs=ukp.idUspevDocs
             INNER JOIN dbo.Review r
              ON ud.idUspevDocs=r.idUspevDocs
               INNER JOIN dbo.Zach z
                ON ud.ik_zach=z.Ik_zach
                 INNER JOIN dbo.Student S
                  ON z.nCode=S.nCode 
                   INNER JOIN dbo.Person Pn
                    ON S.nCode=Pn.nCode
                    INNER JOIN dbo.StudGrup SG
                     ON z.Ik_zach=SG.Ik_zach
                     INNER JOIN dbo.PlanGroup PG
                      ON cu.ik_upContent=PG.ik_upContent
                      INNER JOIN dbo.Commis C
                       ON PG.Ik_Commis=C.Ik_Commis
                        INNER JOIN dbo.ListCommis lc
                         ON c.Ik_Commis=lc.Ik_Commis
                         INNER JOIN dbo.Prepod pre
                          ON pre.itab_n=lc.itab_n
                          INNER JOIN dbo.Person Pe
                           ON pre.nCode=Pe.nCode
                           INNER JOIN dbo.StatusInCommis sic
                           ON lc.Ik_Status=sic.Ik_Status
                            INNER JOIN dbo.kafedra k
                             ON cu.ik_kaf=k.ik_kaf
                             INNER JOIN dbo.Fac f
                              ON k.ik_fac=f.Ik_fac
                                  INNER JOIN dbo.StatusWork SW
                                   ON ukp.idUspevDocs=SW.idUspevDocs
WHERE S.nCode=@nCode AND gr.Ik_grup=@Ik_grup 
      AND sem.n_sem=@n_sem AND vz.iK_vid_zanyat=@iK_vid_zanyat 
      AND dis.iK_disc=@iK_disc AND SG.Ik_prikazZach IS NOT NULL
      AND SG.ik_pricOtch IS NULL AND SW.Ik_TypeEvent=2 AND SW.Approved=1;
GO
